/*
 * Schemamule, a library for automating database schema tasks
 * Copyright (C) 2006, Moses M. Hohman and Rhett Sutphin
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin St., 5th Floor, Boston, MA  02110-1301

 * To contact the authors, send email to:
 * { mmhohman OR rsutphin } AT sourceforge DOT net
 */

package com.oracle2hsqldb.dialect;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.oracle2hsqldb.Column;
import com.oracle2hsqldb.DefaultValue;
import com.oracle2hsqldb.PrimaryKey;
import com.oracle2hsqldb.Sequence;
import com.oracle2hsqldb.Table;
import com.oracle2hsqldb.UniqueConstraint;
import com.oracle2hsqldb.View;


/**
 * @author Moses Hohman
 */
public class Oracle9Dialect extends GenericDialect {
    protected final Logger log = Logger.getLogger(getClass());

    private static final Map TYPES_BY_NAME = new HashMap();
    private static final Map TYPES_BY_TYPE = new HashMap();

    static {
        registerType("TIMESTAMP(3)", "TIMESTAMP", Types.TIMESTAMP);
        registerType("TIMESTAMP(6)", "TIMESTAMP", Types.TIMESTAMP);
        registerType("NUMBER", Types.NUMERIC);
        registerType("INTEGER", Types.INTEGER);
        registerType("VARCHAR2", Types.VARCHAR);
        registerType("CHAR", Types.CHAR);
        registerType("CLOB", Types.CLOB);
        registerType("BLOB", Types.BLOB);
        registerType("FLOAT", Types.FLOAT);
        registerType("LONG", Types.BIGINT);
        

        // treat DATE asymmetrically since an Oracle DATE is actually
        // a TIMESTAMP(0) [non-conforming bastards] and is used as such
        // in schemas we can't change.
        TYPES_BY_NAME.put("DATE", new Integer(Types.TIMESTAMP));
        TYPES_BY_TYPE.put(new Integer(Types.DATE), "DATE");
    }

    private static void registerType(String typeName, int type) {
        Integer objectType = new Integer(type);
        TYPES_BY_NAME.put(typeName, objectType);
        TYPES_BY_TYPE.put(objectType, typeName);
    }

    private static void registerType(String typeName, String formatName, int type) {
        registerType(typeName, type);
        TYPES_BY_TYPE.put(new Integer(type), formatName); //corrected
    }

    public boolean supportsViews() {
        return true;
    }

    public boolean supportsSequences() {
        return true;
    }

    public String getDriverClassName() {
        return "oracle.jdbc.OracleDriver";
    }

    public String getNextSequenceValueSql(String sequenceName) {
        return new StringBuffer(sequenceName).append(".NEXTVAL").toString();
    }

    /**
     * performance improvement over GenericDialect's getTables()
     */
    public Iterator getTables(DataSource dataSource, String schemaName) throws SQLException {
        final List specs = new LinkedList();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.query("SELECT table_name FROM user_tables ", new RowCallbackHandler() {
			public void processRow(ResultSet result) throws SQLException {
				if (!"TOAD_PLAN_TABLE".equals(result.getString("TABLE_NAME"))) {
					specs.add(new Table.Spec(result.getString("TABLE_NAME"), Table.Type.TABLE
							.getJdbcName()));
				}
			}
		});
        jdbcTemplate.query("SELECT view_name, text FROM user_views",
                new RowCallbackHandler() {
                    public void processRow(ResultSet result) throws SQLException {
                        specs.add(new View.Spec(result.getString("VIEW_NAME"), Table.Type.VIEW.getJdbcName(), result.getString("TEXT")));
                    }
                });
        return specs.iterator();
    }

    /**
     * performance improvement over GenericDialect's getColumns()
     */
    public Iterator getColumns(final DataSource dataSource, String schemaName) throws SQLException {
        final List specs = new LinkedList();
        new JdbcTemplate(dataSource).query("SELECT " +
                "column_name, " +
                "table_name, " +
                "data_type, " +
                "NVL(data_precision, data_length) AS column_size," +
                "data_scale AS decimal_digits," +
                "DECODE(nullable, 'Y', 1, 0) AS nullable," +
                "data_default AS column_def " +
                "FROM user_tab_columns",
                new RowCallbackHandler() {
                    public void processRow(ResultSet columns) throws SQLException {
				// retrieve values ahead of time, otherwise you get a stream
				// closed error from Oracle
				String columnName = columns.getString("COLUMN_NAME");
				if (log.isDebugEnabled())
					log.debug("Reading column " + columnName);
				String tableName = columns.getString("TABLE_NAME");
				try {
					int dataType = getType(columns.getString("DATA_TYPE"));
					
					int columnSize = columns.getInt("COLUMN_SIZE");
					int decimalDigits = columns.getInt("DECIMAL_DIGITS");
					boolean isNullable = columns.getBoolean("NULLABLE");
					String columnDef = columns.getString("COLUMN_DEF");
					specs.add(new Column.Spec(tableName, new Column(columnName, dataType, columnSize,
							decimalDigits, isNullable, parseDefaultValue(columnDef, dataType))));
				} catch (IllegalArgumentException e) {
					log.error("Problems with column " + columnName + " from table name  " + tableName);
					throw new SQLException("Problems with column " + columnName + " from table "
							+ tableName, e);
				}
			}
                });
        return specs.iterator();
    }

    /**
     * Superclass's implementation does not work. Seems that the Oracle 9i driver does not support DatabaseMetaData.getPrimaryKeys() well.
     */
    public Iterator getPrimaryKeys(DataSource dataSource, String schemaName) {
        final Map byTableName = new HashMap();
        new JdbcTemplate(dataSource).query("SELECT ucc.column_name, ucc.constraint_name, ucc.table_name " +
                "FROM user_constraints uc INNER JOIN user_cons_columns ucc ON ucc.constraint_name=uc.constraint_name " +
                "WHERE uc.constraint_type='P'",
                new RowCallbackHandler() {
                    public void processRow(ResultSet columns) throws SQLException {
                        if (log.isDebugEnabled()) log.debug("Reading primary key:column " + columns.getString("CONSTRAINT_NAME") + ":" + columns.getString("COLUMN_NAME"));
                        String tableName = columns.getString("TABLE_NAME");
                        if (!byTableName.containsKey(tableName)) {
                            byTableName.put(tableName, new PrimaryKey.Spec(tableName, columns.getString("CONSTRAINT_NAME")));
                        }
                        ((PrimaryKey.Spec) byTableName.get(tableName)).addColumnName(columns.getString("COLUMN_NAME"));

                    }
                });
        return byTableName.values().iterator();
    }

    /**
     * Superclass's implementation does not work. Seems that the Oracle driver does not support DatabaseMetaData.getIndexInfo() well.
     */
    public Iterator getUniqueKeys(DataSource dataSource, String schemaName) {
        final List specs = new LinkedList();
        new JdbcTemplate(dataSource).query("SELECT ucc.column_name, ucc.constraint_name, ucc.table_name " +
                "FROM user_constraints uc INNER JOIN user_cons_columns ucc ON ucc.constraint_name=uc.constraint_name " +
                "WHERE uc.constraint_type='U'",
                new RowCallbackHandler() {
                    public void processRow(ResultSet columns) throws SQLException {
                        String columnName = columns.getString("COLUMN_NAME");
                        String tableName = columns.getString("TABLE_NAME");
                        String constraintName = columns.getString("CONSTRAINT_NAME");
                        if (log.isDebugEnabled()) log.debug("Reading unique constraint:column " + constraintName + ":" + columnName);
                        specs.add(new UniqueConstraint.Spec(tableName, columnName, constraintName));
                    }
                });
        return specs.iterator();
    }

    /**
     * Superclass returns nothing.
     */
    public Iterator getSequences(DataSource dataSource, String schemaName) throws SQLException {
        final List seq = new LinkedList();
        new JdbcTemplate(dataSource).query("SELECT sequence_name, last_number FROM user_sequences",
                new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        String seqName = rs.getString("SEQUENCE_NAME");
                        long seqValue = rs.getLong("LAST_NUMBER");
                        if (log.isDebugEnabled()) log.debug("Reading sequence " + seqName + "; currval=" + seqValue);
                        seq.add(new Sequence(seqName, new Long(seqValue)));
                    }
                });
        return seq.iterator();
    }

    private static final String SYSDATE_STRING = "SYSDATE";
    private static final String SYSTIMESTAMP_STRING = "SYSTIMESTAMP";
    private static final Set NOW_STRINGS = Collections.unmodifiableSet(new HashSet(Arrays.asList(new String[]{SYSDATE_STRING, SYSTIMESTAMP_STRING})));

    public DefaultValue parseDefaultValue(String defaultValue, int type) {
        if (defaultValue != null && NOW_STRINGS.contains(defaultValue.trim())) return DefaultValue.NOW;
        return super.parseDefaultValue(defaultValue, type);
    }

    public String formatDefaultValue(Column column) {
        DefaultValue value = column.defaultValue();
        if (DefaultValue.NOW.equals(value)) {
            if (column.type() == Types.DATE) {
                return SYSDATE_STRING;
            } else {
                return SYSTIMESTAMP_STRING;
            }
        }
        return super.formatDefaultValue(column);
    }

    public int getType(String dataTypeName) {
        if (!TYPES_BY_NAME.containsKey(dataTypeName)) throw new IllegalArgumentException("No registered Oracle type with name " + dataTypeName);
        return ((Integer) TYPES_BY_NAME.get(dataTypeName)).intValue();
    }

    public String getTypeName(int type) {
        final Integer objectType = new Integer(type);
        if (!TYPES_BY_TYPE.containsKey(objectType)) throw new IllegalArgumentException("No registered Oracle type " + type);
        return (String) TYPES_BY_TYPE.get(objectType);
    }
}